package com.sun.officeoa.dao;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.sun.officeoa.bean.Meeting;
import com.sun.officeoa.bean.MeetingRoom;
import com.sun.officeoa.util.StringUtil;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by sang on 2017/8/21.
 */
@Repository
public class MeetingDao {

    @Resource
    private JdbcTemplate jdbcTemplate;



    public List<Meeting> getMeeting7Days(int empid) {
        String sql ="SELECT m.*,mr.`roomname` AS roomname FROM meeting m,meetingroom mr WHERE meetingid IN(SELECT meetingid FROM meetingparticipants WHERE employeeid=?) AND m.`roomid`=mr.`roomid` AND starttime<? AND starttime>?";
        RowMapper<Meeting> rowMapper = new BeanPropertyRowMapper<Meeting>(Meeting.class);
        List<Meeting> meetingList = jdbcTemplate.query(sql,rowMapper,new Object[]{empid,new Timestamp(System.currentTimeMillis() + 7 * 24 * 60 * 60 * 1000),new Timestamp(System.currentTimeMillis())});
        return (meetingList != null && meetingList.size() > 0) ? meetingList : null;
    }

    public List<Meeting> getCanceledMeeting (int empid){
        String sql ="SELECT m.*,mr.`roomname` AS roomname FROM meeting m,meetingroom mr WHERE meetingid IN(SELECT meetingid FROM meetingparticipants WHERE employeeid=?) AND m.`roomid`=mr.`roomid` AND m.status=1";
        RowMapper<Meeting> rowMapper = new BeanPropertyRowMapper<Meeting>(Meeting.class);
        List<Meeting> meetingList = jdbcTemplate.query(sql,rowMapper,new Object[]{empid});
        return (meetingList != null && meetingList.size() > 0) ? meetingList : null;
    }

    public Meeting getMeetingById(int mid){
        List<Meeting> meetingList = null;
        String sql ="select * from meeting WHERE meetingid=?;";
        RowMapper<Meeting> meetingRowMapper = new BeanPropertyRowMapper<Meeting>(Meeting.class);
        meetingList = jdbcTemplate.query(sql,meetingRowMapper,new Object[]{mid});
        return  (meetingList!=null && meetingList.size()>0)?meetingList.get(0):null;
    }

    public List<Meeting> getMyBookingMeeting(int empId){
        List<Meeting> meetingList = null;
        String sql = "SELECT m.*,mr.`roomname` AS roomname FROM meeting m,meetingroom mr WHERE m.`reservationistid`=? AND m.status=0 AND m.`roomid`=mr.`roomid`";
        RowMapper<Meeting> meetingRowMapper = new BeanPropertyRowMapper<Meeting>(Meeting.class);
        meetingList = jdbcTemplate.query(sql,meetingRowMapper,new Object[]{empId});
        return (meetingList != null && meetingList.size() > 0) ? meetingList : null;
    }

    public List<Meeting> getMyMeeting(int empId){
        List<Meeting> meetingList = null;
        String sql = "SELECT m.*,e.`employeename`,mr.`roomname` FROM meeting m,employee e,meetingroom mr WHERE m.`meetingid` IN(SELECT mp.`meetingid` FROM meetingparticipants mp WHERE mp.`employeeid`=?) AND m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid` and m.status=0 AND m.starttime>?";
        RowMapper<Meeting> meetingRowMapper = new BeanPropertyRowMapper<Meeting>(Meeting.class);
        meetingList = jdbcTemplate.query(sql,meetingRowMapper,new Object[]{empId,new Timestamp(System.currentTimeMillis())});
        return (meetingList != null && meetingList.size() > 0) ? meetingList : null;
    }

    public int insertMeeting(MeetingRoom meetingRoom){
        String sql = "INSERT INTO meetingroom(roomnum,roomname,capacity,status,description) VALUES (?,?,?,?,?)";
        return jdbcTemplate.update(sql,new Object[]{meetingRoom.getRoomnum(),meetingRoom.getRoomname(),meetingRoom.getCapacity(),meetingRoom.getStatus(),meetingRoom.getDescription()});
    }

    public int insert(Meeting meeting){
        String sql = "insert into meeting (meetingname,roomid,reservationistid,numberofparticipants,starttime,endtime,reservationtime,description,status) values (?,?,?,?,?,?,?,?,?)";
       // return jdbcTemplate.update(sql,new Object[]{meeting.getMeetingname(),meeting.getRoomid(),meeting.getReservationistid(),meeting.getNumberofparticipants(),meeting.getStarttime(),meeting.getEndtime(),meeting.getReservationtime(),meeting.getDescription(),meeting.getStatus()},new GeneratedKeyHolder().getKey().intValue());
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setString(1, meeting.getMeetingname());
                ps.setInt(2, meeting.getRoomid());
                ps.setInt(3, meeting.getReservationistid());
                ps.setInt(4, meeting.getNumberofparticipants());
                ps.setTimestamp(5, meeting.getStarttime());
                ps.setTimestamp(6, meeting.getEndtime());
                ps.setTimestamp(7, meeting.getReservationtime());
                ps.setString(8, meeting.getDescription());
                ps.setInt(9, meeting.getStatus());
                return ps;
            }
        }, keyHolder);

        return keyHolder.getKey().intValue();
    }

    public int updateMeeting(MeetingRoom meetingRoom){
        String sql = "UPDATE meetingroom set roomnum=?,roomname=?,capacity=?,status=?,description=? WHERE roomid=?";
        return jdbcTemplate.update(sql,new Object[]{meetingRoom.getRoomnum(),meetingRoom.getRoomname(),meetingRoom.getCapacity(),meetingRoom.getStatus(),meetingRoom.getDescription(),meetingRoom.getRoomid()});
    }
    public  List<MeetingRoom> getAllMeetingRoom(){
        List<MeetingRoom> roomList =null;
        String sql="select * from meetingroom";
        RowMapper<MeetingRoom> meetingRowMapper = new BeanPropertyRowMapper<MeetingRoom>(MeetingRoom.class);
        roomList = jdbcTemplate.query(sql,meetingRowMapper);
        return (roomList != null && roomList.size() > 0) ? roomList : null;
    }

    public MeetingRoom roomdetails(int roomid){
        List<MeetingRoom> roomList = null;
        String sql="select * from meetingroom WHERE roomid=?";
        RowMapper<MeetingRoom> meetingRowMapper = new BeanPropertyRowMapper<MeetingRoom>(MeetingRoom.class);
        roomList = jdbcTemplate.query(sql,meetingRowMapper,new Object[]{roomid});
        return (roomList != null && roomList.size() > 0) ? roomList.get(0) : null;
    }

    public int insertMeetingParticipants (int insertId,String[] empids){
        String sql = "insert into meetingparticipants (meetingid,employeeid) values (?,?)";
        try {
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
               @Override
               public void setValues(PreparedStatement ps, int i) throws SQLException {
                   ps.setInt(1, insertId);
                   ps.setString(2, empids[0]);
               }

               @Override
               public int getBatchSize() {
                   return empids.length;
               }
           });
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }


    public List<Meeting> searchMeeting(String meetingname, String roomname, String reservername, String reservefromdate, String reservetodate, String meetingfromdate, String meetingtodate, int page, int count) {

        List<Meeting> list = null;
        String sql = "";
        RowMapper<Meeting> rowMapper = new BeanPropertyRowMapper<Meeting>(Meeting.class);
        if (!StringUtil.isNullOrEmpty(meetingname) && !StringUtil.isNullOrEmpty(roomname) &&!StringUtil.isNullOrEmpty(reservername)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and meetingname=?and roomname=?and employeename=?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname,roomname,reservername,(page - 1) * count,count});
        }
        else if (!StringUtil.isNullOrEmpty(roomname) &&!StringUtil.isNullOrEmpty(reservername)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and roomname=?and employeename=?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{roomname,reservername,(page - 1) * count,count});
        }
        else  if (!StringUtil.isNullOrEmpty(meetingname) && !StringUtil.isNullOrEmpty(roomname)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and meetingname=?and roomname=?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname,roomname,(page - 1) * count,count});
        }
        else  if (!StringUtil.isNullOrEmpty(meetingname)&&!StringUtil.isNullOrEmpty(reservername)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and meetingname=?and employeename=?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname,reservername,(page - 1) * count,count});
        }
        else  if (!StringUtil.isNullOrEmpty(meetingname)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and meetingname=?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname,(page - 1) * count,count});
        }
        else if (!StringUtil.isNullOrEmpty(roomname)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and roomname=?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{roomname,(page - 1) * count,count});
        }
        else  if (!StringUtil.isNullOrEmpty(reservername)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and employeename=?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{reservername,(page - 1) * count,count});
        }
        else if (!StringUtil.isNullOrEmpty(meetingtodate)) {
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                    "and endtime<?limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingtodate,(page - 1) * count,count});
        }else{
            sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`limit ?,?";
            list = jdbcTemplate.query(sql,rowMapper,new Object[]{(page - 1) * count,count});
        }
        return (list != null && list.size() > 0) ? list : null;
    }


    public static void main(String[] args) {

        MeetingDao meetingDao = new MeetingDao();
        List<Meeting> list =  meetingDao.searchMeeting("qqeq","qwe","123","2019-01-22 02:30:12","","","",1,10);
        System.err.println(list);


    }

   public int  getCount(String meetingname, String roomname, String reservername, String reservefromdate, String reservetodate, String meetingfromdate, String meetingtodate){
       List<Meeting> list = null;
       String sql = "";
       RowMapper<Meeting> rowMapper = new BeanPropertyRowMapper<Meeting>(Meeting.class);
       if (!StringUtil.isNullOrEmpty(meetingname) && !StringUtil.isNullOrEmpty(roomname) &&!StringUtil.isNullOrEmpty(reservername)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and meetingname=?and roomname=?and employeename=?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname,roomname,reservername});
       }else if (!StringUtil.isNullOrEmpty(roomname) &&!StringUtil.isNullOrEmpty(reservername)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and roomname=?and employeename=?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{roomname,reservername});
       }
       else  if (!StringUtil.isNullOrEmpty(meetingname) && !StringUtil.isNullOrEmpty(roomname)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and meetingname=?and roomname=?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname,roomname});
       }
       else  if (!StringUtil.isNullOrEmpty(meetingname)&&!StringUtil.isNullOrEmpty(reservername)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and meetingname=?and employeename=?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname,reservername});
       }
       else  if (!StringUtil.isNullOrEmpty(meetingname)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and meetingname=?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingname});
       }
       else if (!StringUtil.isNullOrEmpty(roomname)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and roomname=?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{roomname});
       }
       else  if (!StringUtil.isNullOrEmpty(reservername)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and employeename=?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{reservername});
       }
       else if (!StringUtil.isNullOrEmpty(meetingtodate)) {
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`" +
                   "and endtime<?";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{meetingtodate});
       }else{
           sql = "SELECT m.*,e.`employeename` AS employeename,mr.`roomname` AS roomname FROM meeting m,employee e,meetingroom mr WHERE m.`roomid`=mr.`roomid` AND m.`reservationistid`=e.`employeeid`";
           list = jdbcTemplate.query(sql,rowMapper,new Object[]{});
       }
       return (list != null && list.size() > 0) ? list.size() : 0;
   }


}
